package com.cupk.carManageMaster.common.mapper;

import com.cupk.carManageMaster.common.dto.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @Software: IntelliJ IDEA
 * @Project: car-manage-master
 * @File: AdministrationMapper.java
 * @Author: 箬玉
 * @Description: 行政数据库操作
 * @Date: 2023/6/9 22:02
 */
@Repository
@Mapper
public interface AdministrationMapper {
    @Select("SELECT id FROM type_car WHERE motorcycle_type = #{carType}")
    Long getCarTypeId(@Param("carType") String carType);

    @Insert("INSERT INTO message_car(car_number, type_car_id, purchase_date, create_time, update_time) VALUES (#{carNumber},#{carTypeId},NOW(),NOW(),NOW())")
    void uploadCar(@Param("carNumber") String carNumber, @Param("carTypeId") Long carTypeId);

    @Update("UPDATE message_car SET car_number = #{carNumber}, type_car_id = #{typeCarId}, purchase_date = #{purchaseDate}, update_time = NOW()  WHERE id = #{id}")
    void changeCar(@Param("id") Long id, @Param("carNumber") String carNumber, @Param("purchaseDate") Date purchaseDate, @Param("typeCarId") Long typeCarId);

    @Select("SELECT message_car.id,car_number,motorcycle_type,purchase_date,mileage,is_working FROM message_car JOIN type_car tc on tc.id = message_car.type_car_id AND message_car.is_delete = FALSE")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "car_number", property = "carNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "purchase_date", property = "purchaseDate", jdbcType = JdbcType.DATE),
            @Result(column = "mileage", property = "mileage", jdbcType = JdbcType.BIGINT),
            @Result(column = "is_working", property = "isWorking", jdbcType = JdbcType.BOOLEAN)
    })
    List<CarMessageDto> getCarList();

    @Select("SELECT dispatch_car.id,driver_id,job_number,employee_name,mileage,dispatch_car.task_status,message,schedule_start_time,expected_schedule_end_time,schedule_end_time,longitude,latitude FROM dispatch_car JOIN drive_user du on du.id = dispatch_car.driver_id JOIN job_user ju on ju.id = du.user_id WHERE dispatch_car.car_id = #{carId} AND dispatch_car.is_delete = FALSE")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "driver_id", property = "driverId", jdbcType = JdbcType.BIGINT),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "mileage", property = "mileage", jdbcType = JdbcType.BIGINT),
            @Result(column = "task_status", property = "taskStatus", jdbcType = JdbcType.INTEGER),
            @Result(column = "message", property = "message", jdbcType = JdbcType.VARCHAR),
            @Result(column = "schedule_start_time", property = "scheduleStartTime", jdbcType = JdbcType.DATE),
            @Result(column = "expected_schedule_end_time", property = "expectedScheduleEndTime", jdbcType = JdbcType.DATE),
            @Result(column = "schedule_end_time", property = "scheduleEndTime", jdbcType = JdbcType.DATE),
            @Result(column = "longitude", property = "longitude", jdbcType = JdbcType.DECIMAL),
            @Result(column = "latitude", property = "latitude", jdbcType = JdbcType.DECIMAL)
    })
    List<CarDispatchDto> getCarDispatch(@Param("carId") Long carId);

    @Update("UPDATE message_car SET is_delete = TRUE, update_time = NOW() WHERE id = #{id}")
    void deleteCar(@Param("id") Long id);

    @Select("SELECT dispatch_car.id,driver_id,job_number,employee_name,mileage,dispatch_car.task_status,message,schedule_start_time,MAX(expected_schedule_end_time),schedule_end_time,longitude,latitude FROM dispatch_car JOIN drive_user du on du.id = dispatch_car.driver_id JOIN job_user ju on ju.id = du.user_id WHERE dispatch_car.car_id = #{carId} AND dispatch_car.is_delete = FALSE GROUP BY dispatch_car.id")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "driver_id", property = "driverId", jdbcType = JdbcType.BIGINT),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "mileage", property = "mileage", jdbcType = JdbcType.BIGINT),
            @Result(column = "task_status", property = "taskStatus", jdbcType = JdbcType.INTEGER),
            @Result(column = "message", property = "message", jdbcType = JdbcType.VARCHAR),
            @Result(column = "schedule_start_time", property = "scheduleStartTime", jdbcType = JdbcType.DATE),
            @Result(column = "MAX(expected_schedule_end_time)", property = "expectedScheduleEndTime", jdbcType = JdbcType.DATE),
            @Result(column = "schedule_end_time", property = "scheduleEndTime", jdbcType = JdbcType.DATE),
            @Result(column = "longitude", property = "longitude", jdbcType = JdbcType.DECIMAL),
            @Result(column = "latitude", property = "latitude", jdbcType = JdbcType.DECIMAL)
    })
    CarDispatchDto getCarDispatchNow(@Param("carId") Long carId);

    @Select("SELECT job_user.id,employee_name,entry_time,id_number,age,driving_experience,motorcycle_type,photo_url,phone_number,job_number FROM job_user JOIN drive_user du ON job_user.id = du.user_id JOIN type_car tc ON tc.id = du.type_car_id WHERE du.id = #{driverId} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driving_experience", property = "drivingExperience", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    DriverDto getDriver(@Param("driverId") Long driverId);

    @Select("SELECT id, motorcycle_type FROM type_car WHERE is_delete = FALSE")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "motorcycle_type", property = "motorcycleType", jdbcType = JdbcType.VARCHAR)
    })
    List<CarTypeDto> carTypeList();

    @Select("SELECT message_car.id,car_number,motorcycle_type,purchase_date,mileage,is_working FROM message_car JOIN type_car tc on tc.id = message_car.type_car_id WHERE is_working = FALSE AND type_car_id = #{typeCarId} AND message_car.is_delete = FALSE")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "car_number", property = "carNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "purchase_date", property = "purchaseDate", jdbcType = JdbcType.DATE),
            @Result(column = "mileage", property = "mileage", jdbcType = JdbcType.BIGINT),
            @Result(column = "is_working", property = "isWorking", jdbcType = JdbcType.BOOLEAN)
    })
    List<CarMessageDto> getCarFree(@Param("typeCarId") Long typeCarId);

    @Select("SELECT job_user.id,employee_name,entry_time,id_number,age,driving_experience,motorcycle_type,photo_url,job_number,phone_number FROM job_user JOIN drive_user du ON job_user.id = du.user_id JOIN type_car tc ON tc.id = du.type_car_id WHERE privilege_level = #{privilegeLevel} AND type_car_id = #{typeCarId} AND job_user.is_delete = FALSE")
    @Results({
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driving_experience", property = "drivingExperience", jdbcType = JdbcType.INTEGER),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    List<DriverDto> getDriverFree(@Param("privilegeLevel") Integer privilegeLevel, @Param("typeCarId") Long typeCarId);

    @Update("UPDATE drive_user SET task_status = 0, update_time = NOW() WHERE id = #{driverId}")
    void driverTask(@Param("driverId") Long driverId);

    @Update("UPDATE message_car SET is_working = TRUE, update_time = NOW() WHERE id = #{carId}")
    void carTask(@Param("carId") Long carId);

    @Insert("INSERT INTO dispatch_car(mileage, driver_id, schedule_start_time, expected_schedule_end_time, create_time, update_time, car_id, message) VALUES (#{mileage},#{driverId},#{scheduleStartTime},#{expectedScheduleEndTime},NOW(),NOW(),#{carId},#{message})")
    void dispatchCar(@Param("mileage") Long mileage, @Param("driverId") Long driverId, @Param("scheduleStartTime") Date scheduleStartTime, @Param("expectedScheduleEndTime") Date expectedScheduleEndTime, @Param("message") String message, @Param("carId") Long carId);

    @Select("SELECT message_help.id,is_emergent,alert_time,is_handled,help_seeker,employee_name,entry_time,age,motorcycle_type,driving_experience,id_number,job_number,photo_url,phone_number FROM message_help JOIN drive_user du on du.id = message_help.help_seeker JOIN job_user ju on ju.id = du.user_id JOIN type_car tc on tc.id = du.type_car_id")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT),
            @Result(column = "is_emergent", property = "isEmergent", jdbcType = JdbcType.INTEGER),
            @Result(column = "alert_time", property = "alertTime", jdbcType = JdbcType.DATE),
            @Result(column = "is_handled", property = "isHandled", jdbcType = JdbcType.BOOLEAN),
            @Result(column = "help_seeker", property = "helpSeeker", jdbcType = JdbcType.BIGINT),
            @Result(column = "employee_name", property = "employeeName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "entry_time", property = "entryTime", jdbcType = JdbcType.DATE),
            @Result(column = "age", property = "age", jdbcType = JdbcType.INTEGER),
            @Result(column = "motorcycle_type", property = "typeCar", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driving_experience", property = "drivingExperience", jdbcType = JdbcType.INTEGER),
            @Result(column = "id_number", property = "idNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "job_number", property = "jobNumber", jdbcType = JdbcType.VARCHAR),
            @Result(column = "photo_url", property = "photoUrl", jdbcType = JdbcType.VARCHAR),
            @Result(column = "phone_number", property = "phoneNumber", jdbcType = JdbcType.VARCHAR)
    })
    List<MessageHelpDto> getResortList();

    @Select("SELECT COUNT(*) FROM message_car WHERE car_number = #{carNumber}")
    Integer isExistCarNumber(@Param("carNumber") String carNumber);


}
